package cn.lili.modules.order.order.mapper;

import cn.lili.modules.order.order.entity.dos.OrderFlow;
import cn.lili.modules.order.order.entity.dto.OrderFlowDTO;
import cn.lili.modules.order.order.entity.dto.OrderFlowQueryDTO;
import cn.lili.modules.order.order.entity.dto.UserMoneyDTO;
import cn.lili.modules.order.order.entity.vo.*;
import cn.lili.modules.order.order.entity.vo.DisOrderFlowVO;
import cn.lili.modules.order.order.entity.vo.PerformanceDetailVO;
import cn.lili.modules.order.order.entity.vo.SubUserFlowPriceVO;
import cn.lili.modules.store.entity.vos.StoreFlowPayDownloadVO;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
 * 商家订单流水数据处理层
 *
 * @author Chopper
 * @since 2020/11/17 7:35 下午
 */
public interface OrderFlowMapper extends BaseMapper<OrderFlow> {

    /**
     * 获取结算单的入账流水
     *
     * @param queryWrapper 查询条件
     * @return 入账流水
     */
    @Select("SELECT * FROM li_order_flow ${ew.customSqlSegment}")
    List<StoreFlowPayDownloadVO> getStoreFlowPayDownloadVO(@Param(Constants.WRAPPER) Wrapper<OrderFlow> queryWrapper);

    /**
     * 更新流水状态，将可以分账的流水单号更新为分账中
     * <p>
     * PS:
     * 首先，从li_order_flow表中选择满足以下条件的记录的ID：
     * sql
     * Copy code
     * SELECT o.id
     * FROM li_order_flow o
     * LEFT JOIN (
     * SELECT order_sn
     * FROM li_order_item
     * WHERE after_sale_status <> 'EXPIRED'
     * GROUP BY order_sn
     * ) oi ON o.order_sn = oi.order_sn
     * WHERE o.profit_sharing = 'WAIT_COMPLETE'
     * AND oi.order_sn IS NULL
     * 这部分查询中使用了一个左连接（LEFT JOIN），将li_order_flow表和子查询的结果（在子查询中选择li_order_item表中after_sale_status不等于'EXPIRED'的order_sn
     * ，并进行分组）进行关联。通过使用左连接，我们得到了那些在li_order_item表中没有对应的order_sn的li_order_flow表的记录。
     * <p>
     * 然后，通过WHERE条件来进一步过滤，只选择profit_sharing字段的值为'WAIT_COMPLETE'，且没有对应order_sn的记录。
     * <p>
     * 然后，将选定的记录的profit_sharing字段更新为'PROCESSING'，使用如下语句：
     * sql
     * Copy code
     * UPDATE li_order_flow
     * SET profit_sharing = 'PROCESSING'
     * WHERE id IN (...)
     * 其中，...是上述子查询部分。
     * <p>
     * 这样，执行该SQL语句后，满足条件的记录的profit_sharing字段将被更新为'PROCESSING'。
     * <p>
     * 请确保数据库中存在相应的表和字段，并根据您的具体需求进行适当的调整。如果仍然遇到问题，请检查数据库的结构和数据是否正确，并根据需要进行调试和优化。
     */
    @Select("""
            UPDATE li_order_flow
            SET profit_sharing = 'PROCESSING'
            WHERE id IN (
                SELECT o.id
                FROM (
                    SELECT lof.id
                    FROM li_order_flow lof
                    LEFT JOIN (
                        SELECT order_sn
                        FROM li_order_item
                        WHERE after_sale_status <> 'EXPIRED'
                        GROUP BY order_sn
                    ) oi ON lof.order_sn = oi.order_sn
                    WHERE lof.profit_sharing = 'WAIT_COMPLETE'
                    AND oi.order_sn IS NULL
                ) o
            )
            """)
    void updateProfitSharingStatus();

    @Select("select sum(seller_settlement_price) as sellerSettlementPrice, " +
            "sum(supplier_settlement_price) as supplierSettlementPrice,  " +
            "sum(platform_settlement_price) as platformSettlementPrice, " +
            "sum(green_score) as greenScore, " +
            "sum(store_green_score) as storeGreenScore, " +
            "sum(supplier_green_score) as supplierGreenScore, " +
            "sum(member_green_score) as memberGreenScore, " +
            "sum(site_green_score) as siteGreenScore from li_order_flow where order_sn = #{orderSn}")
    OrderFlow getSumByOrderSn (String orderSn);

    /**
     * 统计所有memberid 的业绩
     * @return
     */
//    @Select("SELECT IFNULL(sum(flow_price),0) AS flowPrice,member_id as memberId, IFNULL(sum(lower_commiss+lower_sub_commiss),0) AS totalCommiss" +
//            " FROM li_order_flow WHERE profit_sharing = 'FINISHED' " +
//            "AND member_id in ${vo.paramSubIds} " +
//            " <if  test='vo.beginDate != null && vo.endDate != null'> and create_time BETWEEN #{vo.beginDate} AND #{vo.endDate} </if> " +
//            "GROUP BY member_id")
    List<OrderFlow> selectSubUserFlowPrice(@Param("vo") SubUserFlowPriceVO vo);

    
    /**
     * 根据工会地区查询本月工会经费
     * @param areaId
     * @return
     */
    @Select("select sum(lof.union_green_score) as unionGreenScore from li_order_flow lof where lof.order_sn in " +
            "(select sn from li_order where consignee_address_id_path like concat(#{areaId},'%') " +
            "and DATE_FORMAT(create_time, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m' ) " +
            "and lof.union_green_score > 0) and lof.profit_sharing in ('WAIT_COMPLETE', 'FINISHED')")
    Double getMonthUnionGreenScoreByAreaId (@Param("areaId") String areaId);

    /**
     * 根据userIds查询订单数
     */
    List<DisOrderFlowVO> selectCountOrderByUserIds(@Param("userIds") List<String> userIds, @Param("lowerCommissId") String lowerCommissId);

    List<DisOrderFlowVO> selectCurrentConsumptionByUserIds(@Param("userIds") List<String> userIds, @Param("lowerCommissId") String lowerCommissId);

    List<DisOrderFlowVO> selectCumulativeConsumptionByUserIds(@Param("userIds") List<String> userIds, @Param("lowerCommissId") String lowerCommissId);

    /**
     * 获取用户金额
     * @param currentId 用户Id
     * @param regionId 区域Id
     * @param beginTime 开始时间
     * @param endTime 结束时间
     * @return 用户金额列表
     */
    UserMoneyDTO getUserMoney(@Param("currentId") String currentId,@Param("regionId") String regionId, @Param("beginTime") String beginTime,@Param("endTime") String endTime);

    /**
     * 获取用户金额
     * @param currentId 用户Id
     * @param regionId 区域Id
     * @param beginTime 开始时间
     * @param endTime 结束时间
     * @param limit 限制条数
     * @return 用户金额列表
     */
    List<UserMoneyDTO> getUserMoneyList(@Param("currentId") String currentId,@Param("regionId") String regionId, @Param("beginTime") String beginTime,@Param("endTime") String endTime,@Param("limit") Integer limit);

    /**
     * 根据dto查找订单明细详情列表
     * @param dto
     * @return
     */
    Page<OrderItemViewVO> getOrderItemBydto(Page page,@Param("dto") OrderFlowQueryDTO dto);

    /**
     * 统计结算总金额
     * @param dto
     * @return
     */
    Double sumOrderPriceByDto(OrderFlowQueryDTO dto);

    /**
     * 店主佣金收益列表
     */
    Page<OrderItemViewVO> getTotalCommissByDisMemberId(Page page, @Param("dto") OrderFlowQueryDTO dto);

    /**
     * 统计结算总佣金
     * @param dto
     * @return
     */
    Double sumOrderComisseByDto(OrderFlowQueryDTO dto);

    /**
     * 查询店主的直推业绩,佣金收益总数
     * @param memberIds
     * @return
     */
    List<OrderItemViewVO> getDistributionFlowPriceAndComissTotal(@Param("userids") List<String> memberIds);

    List<OrderItemViewVO> getDistributionFlowPriceAndComissTotalLastMonth(@Param("userids") List<String> memberIds);

    List<OrderItemViewVO> getDistributionFlowPriceAndComissTotalCurrentMonth(@Param("userids") List<String> memberIds);

    List<OrderItemViewVO> getDistributionFlowPriceAndComissTotalDistribution(@Param("userids") List<String> memberIds);

    List<OrderItemViewVO> getDistributionFlowPriceAndComissTotalType(@Param("userids") List<String> memberIds,@Param("type") Integer type);

    OrderItemViewVO getDistributionFlowPriceAndComissTotalByMonth(@Param("memberId") String memberId, @Param("startTime") String startTime, @Param("endTime")String endTime);

    UserMoneyDTO getUserMoney(@Param("currentId") String currentId,@Param("regionId") String regionId, @Param("beginTime") LocalDateTime beginTime,@Param("endTime") LocalDateTime endTime,@Param("limit") Integer limit);

    /**
     * 根据工会成员查询本月业绩
     * @param page
     * @param queryWrapper
     * @return
     */
    @Select("select lof.create_time as createTime, lof.order_sn as orderSn, lof.nickname as nickname, lof.flow_price as flowPrice, lof.profit_sharing as profitSharing, " +
            "CONCAT((SELECT GROUP_CONCAT(item.goods_name SEPARATOR ', ') FROM li_order_item AS item WHERE item.order_sn = lof.order_sn)) AS goods_name," +
            "CONCAT((SELECT GROUP_CONCAT(item.goods_id SEPARATOR ', ') FROM li_order_item AS item WHERE item.order_sn = lof.order_sn)) AS goods_id " +
            "from li_order_flow lof " +
            "${ew.customSqlSegment} and DATE_FORMAT(lof.create_time, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m') ")
    Page<OrderFlowDTO> getCurrentMonthByMember (Page page, @Param(Constants.WRAPPER) Wrapper<OrderFlow> queryWrapper);

    /**
     * 根据工会成员统计本月已结算金额、待结算金额、退单金额
     * @param lowerCommissId
     * @return
     */
    @Select("select " +
            "SUM(CASE WHEN profit_sharing = 'FINISHED' THEN flow_price END) as settlementMoney," +
            "SUM(CASE WHEN profit_sharing = 'WAIT_COMPLETE' THEN flow_price END) as waitSettlementMoney," +
            "SUM(CASE WHEN profit_sharing in ('ORDER_CANCEL', 'AFTER_SALE') THEN flow_price END) as cancelMoney " +
            "from li_order_flow " +
            "WHERE DATE_FORMAT(create_time, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m') " +
            "and lower_commiss_id = ${lowerCommissId} and ${ew.sqlSegment}")
    PerformanceDetailVO getCurrentMonthStatisticsByMember(@Param(Constants.WRAPPER) Wrapper<OrderFlow> queryWrapper, @Param("lowerCommissId") String lowerCommissId);

    /**
     * 根据工会成员查询上月业绩
     * @param page
     * @param queryWrapper
     * @return
     */
    @Select("select lof.create_time, lof.order_sn, lof.nickname, lof.flow_price, lof.profit_sharing, " +
            "CONCAT((SELECT GROUP_CONCAT(item.goods_name SEPARATOR ', ') FROM li_order_item AS item WHERE item.order_sn = lof.order_sn)) AS goodsName," +
            "CONCAT((SELECT GROUP_CONCAT(item.goods_id SEPARATOR ', ') FROM li_order_item AS item WHERE item.order_sn = lof.order_sn)) AS goodsId " +
            "from li_order_flow lof " +
            "${ew.customSqlSegment} and " +
            "DATE_FORMAT(lof.create_time, '%Y%m' ) = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m') ")
    Page<OrderFlowDTO> getLastMonthByMember (Page page, @Param(Constants.WRAPPER) Wrapper<OrderFlow> queryWrapper);

    /**
     * 根据工会成员统计上月已结算金额、待结算金额、退单金额
     * @param lowerCommissId
     * @return
     */
    @Select("select " +
            "SUM(CASE WHEN profit_sharing = 'FINISHED' THEN flow_price END) as settlementMoney," +
            "SUM(CASE WHEN profit_sharing = 'WAIT_COMPLETE' THEN flow_price END) as waitSettlementMoney," +
            "SUM(CASE WHEN profit_sharing in ('ORDER_CANCEL', 'AFTER_SALE') THEN flow_price END) as cancelMoney " +
            "from li_order_flow " +
            "WHERE DATE_FORMAT(create_time, '%Y%m' ) = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m') " +
            "and lower_commiss_id = ${lowerCommissId} and ${ew.sqlSegment}")
    PerformanceDetailVO getLastMonthStatisticsByMember(@Param(Constants.WRAPPER) Wrapper<OrderFlow> queryWrapper, @Param("lowerCommissId") String lowerCommissId);

    /**
     * 工会经费明细
     * @param page
     * @param queryWrapper
     * @return
     */
    @Select("select lof.create_time as createTime, lof.order_sn as orderSn, " +
            "lof.member_id as memberName, lof.nickname, lof.union_green_score as unionGreenScore, " +
            "lof.profit_sharing as profitSharing, lof.union_commiss_type as unionCommissType," +
            "loi.goods_id as goodsId, loi.goods_name as goodsName " +
            "from li_order_flow lof " +
            "left join li_order_item loi on loi.order_sn = lof.order_sn " +
            "${ew.customSqlSegment} and lof.order_sn in " +
            "(select sn from li_order where consignee_address_id_path like concat(#{areaId},'%') " +
            "and DATE_FORMAT(create_time, '%Y%m' ) = ${month}) order by lof.create_time desc")
    Page<OrderFlow> getUnionMoneyDetail (Page page, @Param("month") String month,@Param("areaId") String areaId, @Param(Constants.WRAPPER) Wrapper<OrderFlow> queryWrapper);

    /**
     * 根据工会统计上月已结算金额、待结算金额、退单金额
     * @param lowerCommissId
     * @return
     */
    @Select("select " +
            "SUM(CASE WHEN profit_sharing = 'FINISHED' THEN union_green_score END) as settlementMoney," +
            "SUM(CASE WHEN profit_sharing = 'WAIT_COMPLETE' THEN union_green_score END) as waitSettlementMoney," +
            "SUM(CASE WHEN profit_sharing in ('ORDER_CANCEL', 'AFTER_SALE') THEN union_green_score END) as cancelMoney " +
            "from li_order_flow lof where lof.union_green_score > 0 and lof.order_sn in " +
            "(select sn from li_order where consignee_address_id_path like concat(#{areaId},'%') " +
            "and DATE_FORMAT(lof.create_time, '%Y%m' ) = ${month})")
    PerformanceDetailVO getUnionMoneyStatisticsDetail (@Param("month") String month,@Param("areaId") String areaId);

    /**
     * 根据工会地区查询本月工会经费
     * @param areaId
     * @return
     */
    @Select("select " +
            "COUNT(CASE WHEN lof.profit_sharing in ('FINISHED', 'WAIT_COMPLETE', 'ORDER_CANCEL', 'AFTER_SALE') THEN lof.id END) as totalCount, " +
            "COUNT(CASE WHEN lof.profit_sharing = 'FINISHED' THEN lof.id END) as finishedCount," +
            "COUNT(CASE WHEN lof.profit_sharing = 'WAIT_COMPLETE' THEN lof.id END) as unFinishedCount," +
            "COUNT(CASE WHEN lof.profit_sharing in ('ORDER_CANCEL', 'AFTER_SALE') THEN lof.id END) as cancelCount " +
            "from li_order_flow lof " +
            "where lof.order_sn in " +
            "(select sn from li_order where consignee_address_id_path like concat(#{areaId},'%')) " +
            "and DATE_FORMAT(lof.create_time, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m' ) " +
            "and lof.union_green_score > 0")
    PerformanceDetailVO getCurrentMonthUnionOrderCountByUnion (@Param("areaId") String areaId);

    /**
     * 根据工会地区查询上月工会经费
     * @param areaId
     * @return
     */
    @Select("select SUM(CASE WHEN lof.profit_sharing = 'FINISHED' THEN lof.union_green_score END) as settlementMoney, " +
            "SUM(CASE WHEN lof.profit_sharing = 'WAIT_COMPLETE' THEN lof.union_green_score END) as waitSettlementMoney," +
            "COUNT(CASE WHEN lof.profit_sharing = 'FINISHED' THEN lof.id END) as finishedCount," +
            "COUNT(CASE WHEN lof.profit_sharing = 'WAIT_COMPLETE' THEN lof.id END) as unFinishedCount," +
            "COUNT(CASE WHEN lof.profit_sharing in ('ORDER_CANCEL', 'AFTER_SALE') THEN lof.id END) as cancelCount " +
            "from li_order_flow lof " +
            "where lof.order_sn in " +
            "(select sn from li_order where consignee_address_id_path like concat(#{areaId},'%') " +
            "and DATE_FORMAT(lof.create_time, '%Y%m' ) = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m') " +
            "and lof.union_green_score > 0)")
    PerformanceDetailVO getLastMonthUnionOrderByUnion (@Param("areaId") String areaId);

    BigDecimal computeContributionValue(@Param("memberId") String memberId,@Param("districtCode") String districtCode, @Param("begin") LocalDate begin, @Param("end") LocalDate end);

    Double totalComiss(@Param("dto") OrderFlowQueryDTO dto);

    @Select("SELECT SUM(lof.flow_price) as lastMonthMoney " +
            "FROM li_order_flow lof " +
            "WHERE DATE_FORMAT(lof.create_time, '%Y%m' ) = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m') " +
            "and lof.profit_sharing = 'FINISHED' " +
            "and lof.lower_commiss_id = ${memberId} and lof.union_green_score > 0")
    Double getLastMonthMoneyByMemberId(@Param("memberId") String memberId);

    @Select("SELECT SUM(lof.flow_price) as lastMonthMoney " +
            "FROM li_order_flow lof " +
            "WHERE DATE_FORMAT(lof.create_time, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m') " +
            "and lof.profit_sharing = 'FINISHED' " +
            "and lof.lower_commiss_id = ${memberId} and lof.union_green_score > 0")
    Double getCurrentMonthMoneyByMemberId(@Param("memberId") String memberId);

    @Select("SELECT SUM(CASE WHEN lof.profit_sharing = 'FINISHED' THEN union_green_score END) as settlementMoney, " +
            "SUM(CASE WHEN lof.profit_sharing = 'WAIT_COMPLETE' THEN union_green_score END) as waitSettlementMoney, " +
            "SUM(CASE WHEN lof.union_commiss_status = 2 THEN union_green_score END) as unionFunds, " +
            "MAX(lof.union_commiss_give_time) as fundsTime, " +
            "MAX(lof.union_commiss_status) as status, " +
            "MAX(lof.union_commiss_type) as commissType " +
            "FROM li_order_flow lof " +
            "WHERE DATE_FORMAT(lof.create_time, '%Y/%m' ) = #{month} and ${ew.sqlSegment}")
    PerformanceDetailVO getUnionFundsByOrderSnMonth(@Param(Constants.WRAPPER) Wrapper<OrderFlow> queryWrapper, @Param("month") String month);



}